using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Acms.SqlDal
{
    public abstract class Base
    {
        protected SqlCommand CreateCommand(string commandText)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = string.Format("[acms].[{0}]", commandText);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            return cmd;
        }

        protected SqlParameter CreateInputParameter(string parameterName, SqlDbType type, object value, int? size, byte? precision, byte? scale)
        {
            SqlParameter p = new SqlParameter();
            p.ParameterName = string.Format("@{0}", parameterName);
            p.Direction = System.Data.ParameterDirection.Input;
            p.SqlDbType = type;
            if (value == null)
            {
                p.Value = DBNull.Value;
            }
            else
            {
                p.Value = value;
            }
            if (size != null)
            {
                p.Size = size.Value;
            }
            if (precision != null)
            {
                p.Precision = precision.Value;
            }
            if (scale != null)
            {
                p.Scale = scale.Value;
            }
            return p;
        }

        protected SqlParameter CreateOutputParameter(string parameterName, SqlDbType type, int? size, byte? precision, byte? scale)
        {
            SqlParameter p = new SqlParameter();
            p.ParameterName = string.Format("@{0}", parameterName);
            p.Direction = System.Data.ParameterDirection.Output;
            p.SqlDbType = type;
            if (size != null)
            {
                p.Size = size.Value;
            }
            if (precision != null)
            {
                p.Precision = precision.Value;
            }
            if (scale != null)
            {
                p.Scale = scale.Value;
            }
            return p;
        }

        protected virtual SqlParameter GetOutputParameter(string name, SqlCommand cmd)
        {
            return cmd.Parameters[string.Format("@{0}", name)];
        }

        protected virtual void SetConnection(SqlCommand command)
        {
            if (command.Connection == null)
            {
                command.Connection = new SqlConnection("Data Source=HOME\\SQLEXPRESS;Initial Catalog=Acms;Integrated Security=True");
            }
        }

        protected Hashtable ExecuteGetItem(SqlCommand cmd)
        {
            System.Collections.Hashtable tmp = null;
            SqlDataReader reader = null;
            try
            {
                SetConnection(cmd);
                cmd.Connection.Open();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    tmp = PopulateHashTable(reader);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                {
                    cmd.Connection.Close();
                }
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return tmp;
        }

        protected Hashtable[] ExecuteGetCollection(SqlCommand cmd)
        {
            System.Collections.Generic.List<System.Collections.Hashtable> tmp = new List<System.Collections.Hashtable>();
            SqlDataReader reader = null;
            try
            {
                SetConnection(cmd);
                cmd.Connection.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tmp.Add(PopulateHashTable(reader));
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (cmd != null && cmd.Connection != null)
                {
                    cmd.Connection.Close();
                }
                if (reader != null)
                {
                    reader.Close();
                }
            }

            return tmp.ToArray();
        }

        protected void ExecuteNonQuery(SqlCommand cmd)
        {
            try
            {
                SetConnection(cmd);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (cmd.Connection != null)
                {
                    cmd.Connection.Close();
                }
            }

        }

        protected virtual object GetValue(SqlDataReader reader, string field)
        {
            if (reader[field] == DBNull.Value)
            {
                return null;
            }
            else
            {
                return reader[field];
            }
        }

        protected void SetReturnValue(ref int val, string name, SqlCommand cmd)
        {
            SqlParameter p = GetOutputParameter(name, cmd);
            if (p == null)
            {
                return;
            }
            else if (p.Value == null || p.Value == DBNull.Value)
            {
                throw new ApplicationException(string.Format("SqlParameter '{0}' for command '{1}' returned a null or a DBNull which is not compatable with an int", p.ParameterName, cmd.CommandText));
            }
            else
            {
                try
                {
                    val = (int)p.Value;
                }
                catch (InvalidCastException)
                {
                    throw new ApplicationException(string.Format("SqlParameter '{0}' for command '{1}' returned a '{2}' which could not be cast to an int", p.ParameterName, cmd.CommandText, p.Value.GetType().ToString()));
                }
            }

        }

        protected void SetReturnValue(ref DateTime val, string name, SqlCommand cmd)
        {
            SqlParameter p = GetOutputParameter(name, cmd);
            if (p == null)
            {
                return;
            }
            else if (p.Value == null || p.Value == DBNull.Value)
            {
                throw new ApplicationException(string.Format("SqlParameter '{0}' for command '{1}' returned a null or a DBNull which is not compatable with a DateTime", p.ParameterName, cmd.CommandText));
            }
            else
            {
                try
                {
                    val = (DateTime)p.Value;
                }
                catch (InvalidCastException)
                {
                    throw new ApplicationException(string.Format("SqlParameter '{0}' for command '{1}' returned a '{2}' which could not be cast to an DateTime", p.ParameterName, cmd.CommandText, p.Value.GetType().ToString()));
                }
            }

        }

        protected abstract System.Collections.Hashtable PopulateHashTable(SqlDataReader reader);

    }
}
